package tech.aistar.dao.impl;

import tech.aistar.dao.IUserDao;
import tech.aistar.entity.User;
import tech.aistar.util.JdbcTemplate;
import tech.aistar.util.JdbcUtil;

import java.sql.*;
import java.util.List;

/**
 * 本类用来演示:
 *
 * @author: success
 * @date: 2022/8/11 10:36 上午
 */
public class UserDaoImpl2 implements IUserDao {
    @Override
    public int save(User user) {
        //通用的DML
        //return JdbcTemplate.executeDML("insert into jdbc_user(username,password,birthday,power) values(?,?,?,?)",user.getUsername(),user.getPassword(),user.getBirthday(),user.getPower());

        //针对save
        return JdbcTemplate.save(user);
    }

    @Override
    public User findByUsername(String username) {
        return null;
    }

    @Override
    public void saveList(List<User> userList) {

        //1. 加载驱动 - 可以省略.
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2. 获取连接
            //准备连接参数
            String url = "jdbc:mysql://localhost:3306/dy?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC";
            String username = "root";
            String password = "root";

            conn = DriverManager.getConnection(url, username, password);

            //3. 创建预编译语句对象
            //准备一条带有参数占位符号的sql语句
            String sql = "insert into jdbc_user(username, password, birthday, power) values(?,?,?,?)";
            pst = conn.prepareStatement(sql);//将这个带有占位符的sql语句提前发送到db-server进行编译

            //4. 发送参数
            for (User u : userList) {
                pst.setString(1,u.getUsername());
                pst.setString(2,u.getPassword());

                //数据日期 - java.sql.Date
                //构造(long time);

                //java.util.Date父类 => 转换成子类
                pst.setDate(3,new java.sql.Date(u.getBirthday().getTime()));

                pst.setInt(4,u.getPower());

                pst.executeUpdate();
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6. 释放资源,注意顺序,最后创建的资源需要先释放

            if(null!=pst){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(null!=conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void saveBatch(List<User> userList) {

        //1. 加载驱动 - 可以省略.
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2. 获取连接
            //准备连接参数
            String url = "jdbc:mysql://localhost:3306/dy?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true";
            String username = "root";
            String password = "root";

            conn = DriverManager.getConnection(url, username, password);

            //3. 创建预编译语句对象
            //准备一条带有参数占位符号的sql语句
            String sql = "insert into jdbc_user(username, password, birthday, power) values(?,?,?,?)";
            pst = conn.prepareStatement(sql);//将这个带有占位符的sql语句提前发送到db-server进行编译

            int count = 0;

            //4. 发送参数
            for (User u : userList) {
                pst.setString(1,u.getUsername());
                pst.setString(2,u.getPassword());

                //数据日期 - java.sql.Date
                //构造(long time);

                //java.util.Date父类 => 转换成子类
                pst.setDate(3,new java.sql.Date(u.getBirthday().getTime()));

                pst.setInt(4,u.getPower());

                //一组参数(4个) -> 发送一次 -> 和db-server交互一次
                //pst.executeUpdate();

                //多个批次(很多组)的参数一起发送到db-server

                //将一组的参数添加到一个批次中
                pst.addBatch();

                if(++count==10000){
                    //发送一个批次命令
                    pst.executeBatch();

                    //清空这个批次
                    pst.clearBatch();
                    count = 0;
                }
            }

            //防止不是10000的整数倍
            if(count!=0){
                pst.executeBatch();
                pst.clearBatch();
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6. 释放资源,注意顺序,最后创建的资源需要先释放

            if(null!=pst){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(null!=conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public int delById(Integer id) {
        //Connection conn = null;
        //PreparedStatement pst = null;
        //try {
        //    conn = JdbcUtil.getConnection();
        //    pst = conn.prepareStatement("delete from jdbc_user where id=?");
        //    pst.setInt(1,id);
        //    return pst.executeUpdate();
        //} catch (SQLException e) {
        //    e.printStackTrace();
        //}finally {
        //    JdbcUtil.close(conn,pst);
        //}
        //return 0;

        //return JdbcTemplate.executeUpdate(conn->{
        //    String sql = "delete from jdbc_user where id=?";
        //    PreparedStatement pst = conn.prepareStatement(sql);
        //    pst.setInt(1,id);
        //    return pst;
        //});

        return JdbcTemplate.executeDML("delete from jdbc_user where id=?",id);
    }

    @Override
    public User getById(Integer id) {
        //return (User) JdbcTemplate.executeQuery(conn->{
        //    PreparedStatement pst = conn.prepareStatement("select * from jdbc_user where id=?");
        //    pst.setInt(1,id);
        //    return pst;
        //},rs->{
        //    User user = null;
        //    if(rs.next()){
        //        //手动映射 - 手动绑定
        //        String uname = rs.getString(2);
        //        String pwd = rs.getString(3);
        //        //java.sql.Date extends java.util.Date
        //        Date birthdate = rs.getDate(4);
        //        Integer power = rs.getInt(5);
        //
        //        //将这些多个不同的数据类型的数据 -> 对象 - 数据在内存中的载体
        //        user = new User(uname,pwd,birthdate,power);
        //        user.setId(id);
        //    }
        //    return user;
        //});

        return JdbcTemplate.queryOne(User.class,"select id,username,password,birthday,power from jdbc_user where id=?",id);
    }

    @Override
    public List<User> getAll(String username) {
        return JdbcTemplate.queryList(User.class,"select * from jdbc_user where username like ?",username);
    }
}
